#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
#include <sqlite3.h>

/**
 * @brief 插入记录
 * @param db
 */
void do_insert(sqlite3 *db)
{ 
    int id=0;
    char name[64]={'\0'};
    int score=0;
    printf("请输入要插入的数据(学号 姓名 成绩):\n");
    scanf("%d %s %d",&id,name,&score);
    char buf[256] = {'\0'};
    sprintf(buf,"insert into student values('%d','%s','%d');",id,name,score);

    char* errmsg = NULL;
    int ret = sqlite3_exec(db,buf,NULL,NULL,&errmsg);
    if (ret!=SQLITE_OK)
    {
        printf("err = %s\n",errmsg);
        sqlite3_free(errmsg);
        return;
    }

}

/**
 * @brief 删除记录
 * @param db
 */
void do_delete(sqlite3 *db)
{
    char name[64]={'\0'};
    printf("请输入要删除的学生(姓名):\n");
    scanf("%s",name);
    char buf[256] = {'\0'};
    sprintf(buf,"delete from student where name = '%s';",name);

    char* errmsg = NULL;
    int ret = sqlite3_exec(db,buf,NULL,NULL,&errmsg);
    if (ret!=SQLITE_OK)
    {
        printf("err = %s\n",errmsg);
        sqlite3_free(errmsg);
        return;
    }
}

int callback(void *para, int num, char **f_value, char **f_name)
{
    for (int i = 0; i < num; i++)
    {
        printf("%s:%s\n",f_name[i],f_value[i]);
    }
    printf("\n");
    return SQLITE_OK;
}

/**
 * @brief 利用回调显示
 * @param db
 */
void do_show(sqlite3 *db)
{
    char buf[256] = "select * from student;";

    char* errmsg = NULL;
    int ret = sqlite3_exec(db,buf,callback,NULL,&errmsg);
    if (ret!=SQLITE_OK)
    {
        printf("err = %s\n",errmsg);
        sqlite3_free(errmsg);
        return;
    }
}

/**
 * @brief 不使用回调显示
 * @param db
 */
void do_show_sample(sqlite3 *db)
{
   char buf[256] = "select * from student;";

    char* errmsg = NULL;
    char** resultp = NULL;
    int nrow=0;
    int ncolumn=0;
    int ret = sqlite3_get_table(db,buf,&resultp,&nrow,&ncolumn,&errmsg);
    if (ret!=SQLITE_OK)
    {
        printf("err = %s\n",errmsg);
        sqlite3_free(errmsg);
        return ;
    }

    char** p=NULL;
    p=resultp+ncolumn;

    for (int i = 0; i < nrow; i++)
    {
        for (int j = 0; j < ncolumn; j++)
        {
            printf("%s:%s\n",resultp[j],p[i*ncolumn+j]);
        }
        
    }
    
}


int main()
{
    sqlite3 *db;
    int n;
    char clean[64];

    if (sqlite3_open("my.db", &db) < 0)
    {
        printf("fail to sqlite3_open : %s\n", sqlite3_errmsg(db));
        return -1;
    }

    while ( 1 )
    {
        printf("**************************************************************\n");
        printf("1: insert record   2: delete record  3: do show record  4: show record 5: quit\n");
        printf("**************************************************************\n");
        printf("please select : ");

        if (scanf("%d", &n) != 1)
        {
            fgets(clean, 64, stdin);
            printf("\n");
            continue;
        }
        switch ( n )
        {
        case 1 :
            do_insert(db);
            break;
        case 2 :
            do_delete(db);
            break;
        case 3 :
            do_show(db);
            break;
        case 4 :
            do_show_sample(db);
            break;
        case 5 :
            sqlite3_close(db);
            exit(0);
        default:
            printf("err option\n");
            break;
        }
    }
    return 0;
}
